USE [InterestProjection]
GO

/****** Object:  StoredProcedure [dbo].[InsertLoanAddon]    Script Date: 09/05/2013 16:46:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



Alter procedure [dbo].[InsertLoanAddon]
(
	@loan_no varchar(16),
	@start_date DATETIME,
	@principal decimal(18,2),
	@term int,	
	@period varchar(16),
	@product varchar(50),
	@error_code int OUT,
	@error_msg varchar(512) OUT
)
AS
BEGIN

	DECLARE @end_date DATETIME
	
	IF @period = 'weekly'
	BEGIN
		SET @end_date = DATEADD(week, @term, @start_date)
	END
	ELSE IF @period = 'monthly'
	BEGIN
		SET @end_date = DATEADD(month, @term, @start_date)
	END
	ELSE IF @period = 'bi-monthly' --period is bi-monthly (forthnightly)
	BEGIN
		SET @end_date = DATEADD(week, @term * 2, @start_date)
	END
	ELSE 
	BEGIN
		SET @end_date = @start_date
	END
	
	BEGIN TRY
		INSERT INTO loan_addon
		(
			loan_no, 
			start_date, 
			end_date,
			principal,
			product, 
			term, 
			upload_date, 
			period
		) 
		VALUES
		(
			@loan_no,
			@start_date,
			@end_date,
			@principal,
			@product, 
			@term, 
			GETDATE(),
			@period
		);
		SET @error_code = 0
	END TRY
	BEGIN CATCH
		SET @error_code = ERROR_NUMBER()
		IF @error_code = 2627 --duplicate primary key
		BEGIN
			SET @error_msg = 'The Loan already exist in the system.'
		END
	END CATCH
END;

GO


